#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : month_report_shanghai.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-08-01 19:26 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receivers = 'dt@iyoujia.com;'
else:
    receivers = 'huangdajie@iyoujia.com;oumingsong@iyoujia.com;liujiantao@iyoujia.com;dt@iyoujia.com;'


def offline_orders(st, et):
    # sql = """SELECT
    #     CONCAT(bo.id,'') 订单id,
    #     c.short_name 城市,
    #     uosc.sname 服务中心,
    #     uo.name 门店,
    #     sc.name 渠道,
    #     CASE bo.order_type WHEN 0 THEN '分销'
    #     WHEN 1 THEN '自建'
    #     WHEN 2 THEN '有家'
    #     ELSE '' END 订单类型,
    #     CONCAT(bo.third_id,'') 第三方订单id,
    #     bo.pay_trade_no 交易号,
    #     date(bo.create_time) 下单时间,
    #     CASE bo.brush_status WHEN 0 THEN '非刷单'
    #     WHEN 1 THEN '刷单'
    #     ELSE '' END 是否刷单,
    #     CASE bo.check_status WHEN 1 THEN '未核对'
    #     when 2 THEN '已通过'
    #     WHEN 3 THEN '已驳回'
    #     WHEN 4 THEN '修改待核对'
    #     ELSE '' END 收款核对状态,
    #     CONCAT(bo.pay_time,'') 交易时间,
    #
    #     CONCAT(bo.checkin_day,'') 入住时间,
    #     CONCAT(bo.checkout_day,'') 离店时间,
    #     CASE bo.cancel_status WHEN 0 THEN
    #         bo.real_income/100
    #     ELSE  bo.cut_part/100 END 房费,
    #
    #     CASE bo.cancel_status WHEN 0 THEN  bo.actual_total/100
    #     ELSE  bo.cut_total/100 END 收费总额,
    #     t.deduct_amount/100 押金,
    #     u.realname 录单人
    #
    #     FROM youjia.orders bo
    #     LEFT JOIN youjia.city c ON c.id=bo.city_id
    #     LEFT JOIN youjia_common.sales_channel sc ON bo.order_channel=sc.id
    #     LEFT JOIN youjia.order_detail od ON bo.id=od.order_id
    #     LEFT JOIN youjia.lod_house lh ON od.house_id=lh.id
    #     LEFT JOIN youjia.user_org uo ON uo.id=lh.work_id
    #     LEFT JOIN youjia.order_deposit t ON bo.id=t.order_id
    #     LEFT JOIN youjia.users u ON bo.creator_id =u.id
    #     left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
    #     WHERE bo.order_type=1
    #     AND bo.city_id<>376
    #     AND bo.order_channel=5
    #     AND bo.cancel_status<>3
    #     AND DATE(bo.create_time) between '{st}' and '{et}'
    #     and (c.id = 13 or uosc.sid =30316 ) -- 城市-上海 和 服务中心-南坪服务中心
    #     GROUP BY bo.id
    #     having 房费 IS NOT NULL AND  收费总额 IS NOT NULL
    #     ORDER BY bo.create_time desc""".format(st=st, et=et)
    sql = """SELECT
        CONCAT(o.id) 订单id,
        lodge.title 房源昵称,
        oe.tenant_name 房客姓名,
        oe.tenant_mobile 房客电话,
       
        DATE(o.checkin_day) 入住日期,
        DATE(o.checkout_day) 离店日期,
        c.city_name 城市,c.id,
        CONCAT(o.lodgeunit_id) 房源id,
        CONCAT(lod_house.id) 房屋id,
        u2.realname 业主姓名,
        u1.realname 线上管家姓名,
        user_org.name 门店,
        uosc.sname 服务中心,
        youjia_common.sales_channel.name 订单来源渠道,
        CASE o.pay_state WHEN '0'THEN '待支付' WHEN '1'THEN '已支付' END 支付状态,
        CASE o.cancel_status WHEN '0' THEN '未取消' ELSE '取消' END 取消状态,
        CASE o.order_type WHEN '0' THEN '分销' WHEN '1' THEN '自建' ELSE '直营' END 订单类型,
        DATEDIFF(DATE(o.checkout_day),DATE(o.checkin_day)) 订单间夜,
        o.actual_total / 100 房客支付,
        o.real_income / 100 实际收入,
        order_extra_charge.fee/100 个性化服务费用,
        
        if(o.brush_status = 1,'刷单','非刷单') 是否刷单,
        oe.remarks 备注
        FROM youjia.orders o
        LEFT JOIN youjia.order_extend oe ON oe.order_id = o.id
        LEFT JOIN youjia.city c ON c.id = o.city_id
        LEFT JOIN youjia_common.sales_channel ON youjia_common.sales_channel.id = o.order_channel
        LEFT JOIN youjia.lod_house ON lod_house.lodge_id = o.lodgeunit_id
        LEFT JOIN youjia.lodge ON lodge.id = o.lodgeunit_id
        LEFT JOIN youjia.user_org ON user_org.id = lod_house.work_id and user_org.is_delete = 0
        LEFT JOIN youjia.users u1 ON u1.id = oe.on_manager_id
        LEFT JOIN youjia.users u2 ON u2.id = lod_house.landlord_id
        LEFT JOIN youjia.order_extra_charge  ON order_extra_charge.order_id = o.id
        left join youjia.user_org_service_center uosc on uosc.sid = lod_house.service_id
        WHERE  o.order_channel in (5,31) 
        and lod_house.city_id <> 376
        -- AND o.brush_status <> 1
        -- and (c.id = 13 or c.id=65 )
        and (c.id = 13 or lod_house.service_id =30316 )
        AND DATE(o.checkout_day)  BETWEEN '{st}' AND '{et}';
        """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_personalize_fee(st, et):
    sql = """SELECT
    CONCAT(o.id) 订单id,
    lodge.title 房源昵称,
    oe.tenant_name 房客姓名,
    oe.tenant_mobile 房客电话,
    DATE(o.create_time) 下单时间,
    DATE(o.checkin_day) 入住日期,
    DATE(o.checkout_day) 离店日期,
    c.city_name 城市,
    CONCAT(o.lodgeunit_id) 房源id,
    CONCAT(lod_house.id) 房屋id,
    CONCAT(lod_house.landlord_id) 业主id,
    u2.realname 业主姓名,
    u1.realname 线上管家姓名,
    user_org.name 门店,
    youjia_common.sales_channel.name 订单来源渠道,
    CASE o.pay_state WHEN '0'THEN '待支付' WHEN '1'THEN '已支付' END 支付状态,
    CASE o.cancel_status WHEN '0' THEN '未取消' ELSE '取消' END 取消状态,
    CASE o.order_type WHEN '0' THEN '分销' WHEN '1' THEN '自建' ELSE '直营' END 订单类型,
    DATEDIFF(DATE(o.checkout_day),DATE(o.checkin_day)) 订单间夜,
    o.actual_total / 100 房客支付,
    o.real_income / 100 实际收入,
    order_extra_charge.fee/100 个性化服务费用,
    oe.remarks 备注
    FROM orders o
    LEFT JOIN order_extend oe ON oe.order_id = o.id
    LEFT JOIN city c ON c.id = o.city_id
    LEFT JOIN youjia_common.sales_channel ON youjia_common.sales_channel.id = o.order_channel
    LEFT JOIN lod_house ON lod_house.lodge_id = o.lodgeunit_id
    LEFT JOIN lodge ON lodge.id = o.lodgeunit_id
    LEFT JOIN user_org ON user_org.id = lod_house.work_id and user_org.is_delete = 0
    LEFT JOIN users u1 ON u1.id = oe.on_manager_id
    LEFT JOIN users u2 ON u2.id = lod_house.landlord_id
    LEFT JOIN `order_extra_charge` ON `order_extra_charge`.order_id = o.id
    WHERE o.city_id = 13
    AND o.brush_status <> 1
    AND DATE(o.checkout_day) BETWEEN '{st}' AND '{et}'""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def process(st=None, et=None):
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    offline_orders_df = offline_orders(st=st, et=et)
    order_personalize_fee_df = order_personalize_fee(st=st, et=et)
    text = """    Dear All:
                附件是 上海及南坪服务中心线下订单与个性化服务费！  

                请查收！
                谢谢！
        如有问题！
        请联系: dt@iyoujia.com
        """
    df_dict = {"线下订单": offline_orders_df, "个性化服务费": order_personalize_fee_df}
    mail_util.distribute_mail(df_dict=df_dict, file_name='线下订单与个性化服务费', text=text, title="线下订单与个性化服务费",
                              receiver=receivers, engine='xlsxwriter', index=False,
                              freeze_panes=(1, 1))


def run():
    try:
        process()
        # process('2019-10-01','2019-10-31')
    except Exception as e:
        mail_util.error_mail("线下订单与个性化服务费", e.__str__())
        log.exception(e)


if __name__ == '__main__':
    run()
